package model.admin;

import java.sql.ResultSet;
import java.util.ArrayList;

import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;

import myutil.Connect;

import vo.BatchDetailsVo;
import vo.Placementvo;
import vo.mixed.CourseListVo;


public class AdminPlacementMo {

	ArrayList<BatchDetailsVo> list = new ArrayList<BatchDetailsVo>();
	ArrayList<Placementvo> palcement_list = new ArrayList<Placementvo>();
	ArrayList<Placementvo> internship_list=new ArrayList<Placementvo>();
	ArrayList<Placementvo> company_list = new ArrayList<Placementvo>();
	ArrayList<Placementvo> editstudentlist= new ArrayList<Placementvo>();
	ArrayList<Placementvo> companynamelist=new ArrayList<Placementvo>();
	
public ArrayList<BatchDetailsVo> getPlacementBatch() {
		
		String query;
		query="select * from batch_details order by start_year desc";
		System.out.println(query);
		Connect c=new Connect();
		try {
			ResultSet rs= c.getResult(query);
			while(rs.next()){
				BatchDetailsVo vo= new BatchDetailsVo();
				vo.setBatch_id(rs.getInt("batch_id"));
				vo.setBatch_name(rs.getString("batch_name"));
				vo.setStart_date(rs.getString("start_date"));
				vo.setEnd_date(rs.getString("end_date"));
				vo.setStudent_count(rs.getInt("student_count"));
				vo.setStart_year(rs.getInt("start_year"));
				vo.setIncharge_id(rs.getInt("incharge_id"));
				vo.setDescription(rs.getString("description"));
				list.add(vo);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	public ArrayList<Placementvo> getPlacementdetails(int batch_id) {
	
	String query;
	query="SELECT sd.student_id,roll_number, student_name, company_name, package_offered, visit_date FROM student_batch sb, student_placed sp, student_details sd, company_visit cv, company_details cd WHERE sb.batch_id = "+batch_id+" AND sb.student_batch_id = sp.student_batch_id AND sb.student_id = sd.student_id AND sp.company_visit_id = cv.company_visit_id AND cv.company_id = cd.company_id AND cv.placement = 'yes'";
	System.out.println(query);
	Connect c = new Connect();
	try {
		ResultSet rs=c.getResult(query);
		while(rs.next()){
			Placementvo vo= new Placementvo();
			vo.setStudent_id(rs.getInt("student_id"));
			vo.setRoll_number(rs.getString("roll_number"));
			vo.setStudent_name(rs.getString("student_name"));
			vo.setCompany_name(rs.getString("company_name"));
			vo.setPackage_offered(rs.getString("package_offered"));
			vo.setVisit_date(rs.getString("visit_date"));
			palcement_list.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
		return palcement_list;
	}
	
	public ArrayList<Placementvo> getInternshipdetails(int batch_id) {
		
	String query;
	query="SELECT sd.student_id,roll_number, student_name, company_name, package_offered, visit_date FROM student_batch sb, student_placed sp, student_details sd, company_visit cv, company_details cd WHERE sb.batch_id ="+batch_id+" AND sb.student_batch_id = sp.student_batch_id AND sb.student_id = sd.student_id AND sp.company_visit_id = cv.company_visit_id AND cv.company_id = cd.company_id AND cv.internship = 'yes'";
	System.out.println(query);
	Connect c=new Connect();
	try {
		ResultSet rs=c.getResult(query);
		while(rs.next()){
			Placementvo vo= new Placementvo();
			vo.setStudent_id(rs.getInt("student_id"));
			vo.setRoll_number(rs.getString("roll_number"));
			vo.setStudent_name(rs.getString("student_name"));
			vo.setCompany_name(rs.getString("company_name"));
			vo.setPackage_offered(rs.getString("package_offered"));
			vo.setVisit_date(rs.getString("visit_date"));
			internship_list.add(vo);
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return internship_list;
	}
	
	public ArrayList<Placementvo> getCompanydetails(int batch_id) {
		
		String query;
		query="SELECT company_name, visit_date, package_offered,student_taken_placement,student_taken_intern FROM company_details cd, company_visit cv WHERE cv.batch_id ="+batch_id+" AND cv.company_id = cd.company_id";
		System.out.println(query);
		Connect c= new Connect();
		try {
			ResultSet rs=c.getResult(query);
			while(rs.next()){
				Placementvo vo= new Placementvo();
				vo.setCompany_name(rs.getString("company_name"));
				vo.setVisit_date(rs.getString("visit_date"));
				vo.setPackage_offered(rs.getString("package_offered"));
				vo.setStudent_taken_placement(rs.getInt("student_taken_placement"));
				vo.setStudent_taken_internship(rs.getInt("student_taken_intern"));
				company_list.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return company_list;
	}
	
	public ArrayList<Placementvo> editstudentcompany(int student_id) {
		
		String query;
		query="select sd.student_id,student_name,roll_number,batch_id from student_details sd,student_batch sb where sd.student_id="+student_id+" and sd.student_id=sb.student_id";
		System.out.println(query);
		Connect c=new Connect();
		try {
			ResultSet rs= c.getResult(query);
			while(rs.next()){
				Placementvo vo= new Placementvo();
				vo.setStudent_id(rs.getInt("student_id"));
				vo.setStudent_name(rs.getString("student_name"));
				vo.setRoll_number(rs.getString("roll_number"));
				vo.setBatch_id(rs.getInt("batch_id"));
				editstudentlist.add(vo);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return editstudentlist;
	}
	
	public ArrayList<Placementvo> getCompanyplacementNameList(int student_id) {
		
		String query;
		query="SELECT company_name,cd.company_id FROM company_visit cv, company_details cd, student_batch sb WHERE sb.student_id ="+student_id+" AND sb.batch_id = cv.batch_id AND cv.company_id = cd.company_id and cv.placement='yes'";
		System.out.println(query);
		Connect c= new Connect();
		try {
			ResultSet rs= c.getResult(query);
			while(rs.next()){
				Placementvo vo= new Placementvo();
				vo.setCompany_id(rs.getInt("company_id"));
				vo.setCompany_name(rs.getString("company_name"));
				companynamelist.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return companynamelist;
	}
	
	public ArrayList<Placementvo> getCompanyinternshipNameList(int student_id) {
		
		String query;
		query="SELECT company_name,cd.company_id FROM company_visit cv, company_details cd, student_batch sb WHERE sb.student_id ="+student_id+" AND sb.batch_id = cv.batch_id AND cv.company_id = cd.company_id and cv.internship='yes'";
		System.out.println(query);
		Connect c= new Connect();
		try {
			ResultSet rs= c.getResult(query);
			while(rs.next()){
				Placementvo vo= new Placementvo();
				vo.setCompany_id(rs.getInt("company_id"));
				vo.setCompany_name(rs.getString("company_name"));
				companynamelist.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return companynamelist;
	}
	
	public void updateStudentPlacement(int student_id,int batch_id,int company_id,String type){
		
		String query,query1,query2,query3;
		query="select company_visit_id from company_visit cv,company_details cd where cd.company_id="+company_id+" and cv.batch_id="+batch_id+" and cd.company_id=cv.company_id ";
		System.out.println(query);
		Connect c= new Connect();
		int company_visit_id=0;
		int student_batch_id=0;
		try {
			ResultSet rs= c.getResult(query);
			while(rs.next()){
				company_visit_id=rs.getInt("company_visit_id");
				}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		query1="select student_batch_id from student_batch where batch_id="+batch_id+" and student_id="+student_id;
		System.out.println(query1);
		Connect co= new Connect();
		try {
			ResultSet rs= co.getResult(query1);
			while(rs.next()){
				student_batch_id=rs.getInt("student_batch_id");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		Connect con= new Connect();
		query2="update student_placed set company_placement_id="+company_id+",placement='yes',company_visit_id="+company_visit_id+" where student_batch_id="+student_batch_id;
		query3="update company_visit set student_taken_placement=student_taken_placement+"+1+" where company_visit_id="+company_visit_id;
		System.out.println(query2);
		System.out.println(query3);
		try {
			con.dml(query2);
			con.dml(query3);
			con.logger(type, "Update");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
public void updateStudentInternship(int student_id,int batch_id,int company_id,String type){
		
		String query,query1,query2,query3;
		query="select company_visit_id from company_visit cv,company_details cd where cd.company_id="+company_id+" and cv.batch_id="+batch_id+" and cd.company_id=cv.company_id ";
		System.out.println(query);
		Connect c= new Connect();
		int company_visit_id=0;
		int student_batch_id=0;
		try {
			ResultSet rs= c.getResult(query);
			while(rs.next()){
				company_visit_id=rs.getInt("company_visit_id");
				}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		query1="select student_batch_id from student_batch where batch_id="+batch_id+" and student_id="+student_id;
		System.out.println(query1);
		Connect co= new Connect();
		try {
			ResultSet rs= co.getResult(query1);
			while(rs.next()){
				student_batch_id=rs.getInt("student_batch_id");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		Connect con= new Connect();
		query2="update student_placed set company_internship_id="+company_id+",internship='yes',company_visit_id="+company_visit_id+" where student_batch_id="+student_batch_id;
		query3="update company_visit set student_taken_internship=student_taken_placement+"+1+" where company_visit_id="+company_visit_id;
		System.out.println(query2);
		try {
			con.dml(query2);
			con.dml(query3);
			con.logger(type, "Update");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
